package com.xkh.common.util;

import cn.hutool.core.date.DateTime;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Date;

/**
 * @author xkh
 * @date 2022/10/8 10:23
 */
public class ExcelUtil {

    /**
     * 原始POI 只能按照workbook->sheet->line->cell的顺序进行循环解析，造成了如此繁琐的代码
     * 查询计算困难
     *
     * @throws IOException
     */
    @Test
    public void excelPoi() throws IOException {
        //如果表格有标题头 先
        String filePath = "C:\\Users\\Administrator\\Desktop\\1.xls";
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    int cells = row.getPhysicalNumberOfCells();
                    for (int j = 0; j < cells; j++) {
                        System.out.print("[" + i + "-" + j + "]");
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            Object value = "";
                            value = cell.getRow().getCell(j);
                            switch (cell.getCellTypeEnum()) {
                                case STRING: // string
                                    value = cell.getStringCellValue();
                                    break;
                                case BLANK: // 空
                                    break;
                                case BOOLEAN: // boolean
                                    value = cell.getBooleanCellValue();
                                    break;
                                case NUMERIC: // number
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) { // date number
                                        Date date = cell.getDateCellValue();
                                        value = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                                    } else {
                                        cell.setCellType(CellType.STRING);
                                        value = cell;
                                    }
                                    break;
                                case ERROR:
                                    throw new RuntimeException("data type mistaken");
                                default:
                                    break;
                            }
                            System.out.println(value);
                        }
                    }
                }
            }
        }
    }
}
